/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ttt.nor2.importdata.txt;

import com.ttt.nor2.util.ExceptionUtil;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Locale;
import org.apache.log4j.Logger;
import org.joda.time.DateTime;

/**
 *
 * @author Administrator
 */
public class ImportBilling extends ImportFile {

    private Logger logger = Logger.getLogger(ImportBilling.class);
    // private String month; // get value from filename N_RO2_0909.txt //get value from jndi
    private PreparedStatement pstmt;
    private ResultSet rs;

    public ImportBilling() {
    }

    public void savedata() {

        //call function manage month
        try {
            logger.debug("row = " + row);
            logger.debug("find strint 'Sum' result : " + line.indexOf("Sum"));
            logger.debug("find strint 'Count' result : " + line.indexOf("Count"));

            if (row != 1 && row != 2 && line.indexOf("Sum") < 0 && line.indexOf("Count") < 0) {

                //get data from cs
                //Hashtable ht = getDataFromCS(rent_month[3]);//paramert phoneno
                long cust_id = 0;
                //long network_id = 0;
                /*if (ht.size() > 0) {
                cust_id = mtbCustomer(ht, rent_month[4]);// insert customer
                logger.info("customer id[" + cust_id + "]");
                mtbCustomer(cust_id, rent_month[5]);//param name, status_cust
                // manage table network
                network_id = mtNetwork(rent_month[3]);// param phone
                logger.info("network id[" + network_id + "]");
                //} else {
                cust_id = mtbCustomer(rent_month[5], rent_month[4]);
                network_id = mtNetwork();
                }
                 */
                cust_id = mtbCustomer(rent_month[5], rent_month[4]);//param res, name
                // manage table phone #param phoneno, office_desc,  status,  province_id
                long phone_id = mtbPhone(rent_month[3], rent_month[0], rent_month[1], rent_month[0].substring(2, 4));
                logger.debug("phone id[" + phone_id + "]");
                //mange phonerent param
                //mtbPhoneRent(cust_id, rent_month[2], phone_id, network_id);
                //manage table billing
                mtbBilling(0, rent_month[2], cust_id, phone_id);//index current
                logger.info(row + ".) Save data sucess.. done");
            }
        } catch (Exception e) {
            logger.error("Exception call fucntion error " + ExceptionUtil.getStackTrace(e));
        } finally {
            row++;
        }// end finally

    }// end savedata

    protected void mtbBilling(int index, String rent_id, long cust_id, long phone_id) {

        //update all data status = "F"
        String sql = "";
        int rentid = Integer.parseInt(rent_id.substring(0, rent_id.indexOf("-")).trim());
        try {

            // find data old in tb billing
            for (int i = 0; i < 12; i++) {
                // check data old in tb billing
                sql = "SELECT bill_id from billings where onmonth=? and rent_id=? and cust_id=? and phone_id=?";
                pstmt = conn.prepareStatement(sql);
                DateTime dd;
                logger.debug("year[" + rtn.getYear() + "]month[" + rtn.getMon() + "]");
                dd = new DateTime(rtn.getYear(), rtn.getMon(), 1, 0, 0, 0, 0);
                logger.debug("arrayMonth[" + rtn.getArrMonth()[i] + "]");
                DateTime dt = dd.plusMonths(-rtn.getArrMonth()[i]);
                String month = dt.toString("yyyyMM", Locale.US);

                pstmt.setString(1, month);
                pstmt.setString(2, rent_month[2 + index]);
                pstmt.setLong(3, cust_id);
                pstmt.setLong(4, phone_id);
                rs = pstmt.executeQuery();

                if (rs.next()) {
                    long bill_id = rs.getInt("bill_id");
                    sql = "update billings  set bill_status = 'N' where bill_id=?";
                    try {
                        pstmt = conn.prepareStatement(sql);
                        pstmt.setLong(1, bill_id);
                        int upRowY = pstmt.executeUpdate();
                        logger.info("Update billing [" + bill_id + "] " + upRowY + " recs.");
                        pstmt.close();
                    } catch (Exception e) {
                        logger.error("Exception update billing status = Y error " + ExceptionUtil.getStackTrace(e));
                    }

                } else {
                    if (!rent_month[rtn.getIndexMonth() + i].equalsIgnoreCase("")) {
                        long bill_id = genkey("bill_id");
                        sql = "insert into billings (onmonth, rentpaid, rent_id, bill_id, cust_id, phone_id)values( ?, ?, ?, ?, ?, ?)";
                        pstmt = conn.prepareStatement(sql);
                        pstmt.setString(1, month);
                        logger.debug("index insert[" + rent_month[rtn.getIndexMonth() + i] + "]");
                        pstmt.setString(2, rent_month[rtn.getIndexMonth() + i].replace(",", ""));
                        pstmt.setInt(3, rentid);
                        pstmt.setLong(4, bill_id);
                        pstmt.setLong(5, cust_id);
                        pstmt.setLong(6, phone_id);
                        pstmt.executeUpdate();
                        pstmt.close();
                        logger.info("insert to billing [" + month + "][" + rent_month[rtn.getIndexMonth() + i] + "][" + rent_month[1 + index] + "]");
                    }

                }
                rs.close();
                pstmt.close();


            }//end for loop month

            if (!rent_month[18].equalsIgnoreCase("")) {
                sql = "SELECT bill_id from billings where onmonth=? and rent_id=? and cust_id=? and phone_id=?";
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, "000000");
                pstmt.setString(2, rent_month[2 + index]);
                pstmt.setLong(3, cust_id);
                pstmt.setLong(4, phone_id);
                rs = pstmt.executeQuery();
                if (rs.next()) {
                    long bill_id = rs.getInt("bill_id");
                    sql = "update billings  set bill_status = 'N', rentpaid=? where bill_id=?";
                    try {
                        pstmt = conn.prepareStatement(sql);
                        pstmt.setString(1, rent_month[18]);
                        pstmt.setLong(2, bill_id);
                        int upRowY = pstmt.executeUpdate();
                        logger.info("Update billing [" + bill_id + "] " + upRowY + " recs.");
                        pstmt.close();
                    } catch (Exception e) {
                        logger.error("Exception update billing status = Y error " + ExceptionUtil.getStackTrace(e));
                    }

                } else {
                    long bill_id = genkey("bill_id");
                    sql = "insert into billings (onmonth, rentpaid, rent_id, bill_id, cust_id, phone_id)values( ?, ?, ?, ?, ?, ?)";
                    pstmt = conn.prepareStatement(sql);
                    pstmt.setString(1, "000000");
                    pstmt.setString(2, rent_month[18].replace(",", ""));
                    pstmt.setInt(3, rentid);
                    pstmt.setLong(4, bill_id);
                    pstmt.setLong(5, cust_id);
                    pstmt.setLong(6, phone_id);
                    pstmt.executeUpdate();
                    pstmt.close();
                    logger.info("insert to billing [000000][" + rent_month[18] + "]");

                }
            }
        } catch (Exception ex) {
            logger.error("Exception  " + ExceptionUtil.getStackTrace(ex));
        }

    }
}
